package init

import groovy.sql.Sql

////房 2014-02-28  add  更改实体后执行变更表结构的sql,别人更新的时候代码后,就不会手工更改数据库
class BootSqlService {
    def dataSource
    def grailsApplication
    def utilsService

    def zhixing() {
        Sql sql = new Sql(dataSource)
        def list = sqlList()
//        log.debug(list.size())
        list.each {
            //放循环里 防止出错后不再向下执行
            try {
//                log.debug(it)
                sql.execute(it)
            }
            catch (Exception e) {
                println(e.toString() + "  " + it)
            }
        }

    }

    def createIndex(tableName, indexName, zdName) {
        //  DECLARE flag NUMBER ;BEGIN SELECT count(*)   INTO flag  from user_ind_columns where index_name='MAT_QCQM_TUIKJE' ;IF flag =0  THEN EXECUTE IMMEDIATE 'CREATE INDEX "MAT_QCQM_TUIKJE"  ON "MAT_QCQM" ("TUIKJE" ASC)' ; END IF ; END ;
        def str = "  DECLARE flag NUMBER ;BEGIN SELECT count(*)   INTO flag  from user_ind_columns where index_name='" + indexName + "' ;IF flag =0  " +
                "THEN EXECUTE IMMEDIATE 'CREATE INDEX \"" + indexName + "\"  ON \"" + tableName + "\"(\"" + zdName + "\" ASC)' ; END IF ; END ;"
        str
    }

    def createOneLink(map) {
        def str = ""
        str += "CREATE  DATABASE link " + '"' + map.name + '"'
        str += " CONNECT TO " + map.username + "  identified BY " + '"' + map.password + '" '
        str += " USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = " + map.url + " )(PORT = " + map.port + " ))) (CONNECT_DATA = (SERVICE_NAME = " + map.server + " )))'"
        return str

    }


    private List<?> sqlList() {
        // 业务配置中出库的金额的精度

        def str1 = "", list = []
        // 创建 视图   ,如果存在同名的表 删掉
        str1 = "DROP TABLE IF EXISTS `USER_WAITTING_TASK`"
        list << str1
        str1 = "DROP TABLE IF EXISTS `FBH_COMMTASK` "
        list << str1

        //创建 流程 非驳回环节的 视图
        str1 = """   CREATE  VIEW  FBH_COMMTASK AS
 SELECT
 --  非驳回环节的 视图
*
FROM
   COMMON_TASK INSTANCE
WHERE
 INSTANCE .date_Created >= (
   SELECT
      MAX(date_Created)
   FROM
      COMMON_TASK,
      Window_Config
   WHERE
      COMMON_TASK.ht_Name = Window_Config.STEP_NAME
   AND Window_Config.process_Name = COMMON_TASK.TEMPLATE_NAME
   AND Window_Config.is_Start_Task = 1
AND COMMON_TASK.piid = INSTANCE .piid
)

"""
        list << str1
        def str = """CREATE  view  USER_WAITTING_TASK AS
        select t.id_ as task_id,
               t.execution_id_ as execution_id,
               t.proc_inst_id_ as piid,
               t.name_ as ht_name_zh,
               t.parent_task_id_ as parent_task_id,
               t.task_def_key_ as ht_name,
               (t.assignee_) as assignee,
               t.create_time_ as create_time,
               t.due_date_ as due_date,
               arp.name_, arp.key_,arv.text_ as fqrusername,smm.realname as fqr,dept.name as fqbm,dept.code as fqbmcode
          from act_ru_task t
          left join act_re_procdef arp
            on t.proc_def_id_ = arp.id_
        \tleft join ACT_RU_VARIABLE  arv
        \ton t.proc_inst_id_ = arv.proc_inst_id_
        \tand arv.name_ = 'fqr'
         left join STAFF_MEMBER  smm
        on smm.username = arv.text_
         LEFT JOIN DEPARTMENT  dept
        on dept.id = smm.department_id
         where t.assignee_ is not null
         union
        select t.id_, t.execution_id_, t.proc_inst_id_, t.name_, t.parent_task_id_,
               t.task_def_key_, (sm.username) as assignee_, t.create_time_, t.due_date_,
               arp.name_, arp.key_,arv.text_ as fqrusername,smm.realname as fqr,dept.name as fqbm,dept.code as fqbmcode
          from act_ru_task t
          left join act_ru_identitylink ari
            on t.id_ = ari.task_id_
          left join act_re_procdef arp
            on t.proc_def_id_ = arp.id_
          left join staff_member_authority sma
            on ari.group_id_ = sma.authority_id
          left join staff_member  sm
            on sm.id = sma.staff_member_id
        left join ACT_RU_VARIABLE  arv
         on t.proc_inst_id_ = arv.proc_inst_id_
         and arv.name_ = 'fqr'
         left join STAFF_MEMBER  smm
        on smm.username = arv.text_
         LEFT JOIN DEPARTMENT  dept
        on dept.id = smm.department_id
         where sm.username is not null
         and t.ASSIGNEE_ is  null
         union
         select t.id_, t.execution_id_, t.proc_inst_id_, t.name_, t.parent_task_id_,
                t.task_def_key_, (ari.user_id_), t.create_time_, t.due_date_,
                arp.name_, arp.key_,arv.text_ as fqrusername,smm.realname as fqr,dept.name as fqbm,dept.code as fqbmcode
           from act_ru_task t
           left join act_ru_identitylink ari
             on t.id_ = ari.task_id_
           left join act_re_procdef arp
             on t.proc_def_id_ = arp.id_
        left join ACT_RU_VARIABLE  arv
        on t.proc_inst_id_ = arv.proc_inst_id_
        and arv.name_ = 'fqr'
         left join STAFF_MEMBER  smm
        on smm.username = arv.text_
         LEFT JOIN DEPARTMENT  dept
        on dept.id = smm.department_id
          where ari.user_id_ is not null
         and t.ASSIGNEE_ is  null
        """
        list << str
        //创建  系统首页代办任务的视图
        str1 = """
       CREATE   VIEW sydb as
        SELECT
              uwt.task_id,
              uwt.piid,
              uwt.ht_name_zh,
              uwt.ht_name,
              uwt.assignee,
              date_format(
                uwt.create_time,
                'yyyy-mm-dd hh24:mi:ss'
              ) AS create_time,
              cd.title,
              cd.creater_id,
              cd.creater_dpt_id,
                date_format(
                cd.creater_time,
                'yyyy-mm-dd hh24:mi:ss'
              ) AS creater_time,
              uwt.key_,
              cd.flow_id,
              uwt.name_,
              wg.start_step_name,
              wg.his_form_nameurl,
              wg.his_dataurl,
              wg.cur_dataurl,
              wg.start_stepurl, 
              wg.tache_form_name,
              wg.is_start_task,
              wg.batch_url,
              wg.saveurl,
              uwt.fqr AS fqr,
              uwt.fqbm AS fqbm,
              cd.commtask_code as commtask_code,
              wg.xmmc as xmmc,
              wg.picurl as picurl,
              wg.is_Batch as plcl
               FROM
               user_waitting_task uwt
            LEFT JOIN window_config wg ON uwt.ht_name = wg.step_name
            LEFT JOIN common_task cd ON uwt.piid = cd.piid
            AND cd.template_name = wg.process_name
            AND cd.task_id = uwt.task_id
            ORDER BY
              uwt.create_time DESC

    """
        list << str1

  /* //创建  参与过的流程视图
        str1 = """
       CREATE   VIEW bllc as
        SELECT
                  cyview.*, NVL (
                    task.title,
                    (
                      SELECT
                        title
                      FROM
                        common_task
                      WHERE
                        piid = cyview.piid
                      AND title IS NOT NULL
                      AND ROWNUM = 1
                    )
                  ) AS title,-- 业务描述
                  task.task_id,-- 参与的第一个环节 taskid
                  task.ht_name_zh,-- 参与的第一个环节名称
                  task.TOP_FLOW_ID,-- 参与的第一个环节的 TOP_FLOW_ID
                  task.template_Name as process,-- 流程英文名称
                  task.TEMPLATE_NAME_ZH as processName,-- 流程中文名
                  task.STATUS,-- 参与的第一个环节的状态
                  task.commtask_Code,-- 单据编号
                  task.ht_name, -- 参与的第一个环节英文名
                  task.XMMC, -- 参与的第一个环节项目名称
                   b.name  as FQBM,-- 发起部门名称
                  c.REALNAME as fqr ,--发起人名称
                  TO_CHAR (
                    task.creater_time,
                    'yyyy-mm-dd hh24:mi:ss'
                  ) AS create_time  -- 参与的第一个环节创建时间
                FROM
                  (
                    SELECT
                      MIN (ID) AS ID,--参与的第一个环节,把min 去掉就可以查到某用户参与的所有环节了
                      PIID,    -- 流程 piid
                      DATE_CREATED,--流程创建时间
                      endTime,--流程结束时间
                      USERNAME AS cyusername--参与人username,

                    FROM
                      (
                        SELECT
                          TASK. ID,
                          task.PIID,
                          TO_CHAR (
                            AHP.START_TIME_,
                            'yyyy-mm-dd hh24:mi:ss'
                          ) DATE_CREATED,
                          TO_CHAR (
                            AHP.END_TIME_,
                            'yyyy-mm-dd hh24:mi:ss'
                          ) AS endTime,
                          EE.USERNAME
                        FROM
                          COMMON_TASK task,
                          ACT_HI_PROCINST ahp,
                          STAFF_MEMBER  ee
                        WHERE
                          task.piid = AHP.PROC_INST_ID_
                        AND EE. ID = TASK.OPERATOR_ID
                      )
                    GROUP BY
                      PIID,
                      DATE_CREATED,
                      endTime,
                      USERNAME
                    ORDER BY
                      date_created DESC
                  ) cyview
                LEFT OUTER JOIN  COMMON_TASK task ON task.id = cyview.id
                LEFT OUTER JOIN department  b ON task.CREATER_DPT_ID = b. ID
                LEFT OUTER JOIN STAFF_MEMBER  c ON task.CREATER_ID = c. ID

    """
        list << str1

 //创建  发起过的流程视图
        str1 = """
       CREATE   VIEW fqlc as
      --- 我发起的流程
                SELECT
                  cyview.*, NVL (
                    task.title,
                    (
                      SELECT
                        title
                      FROM
                        common_task
                      WHERE
                        piid = cyview.piid
                      AND title IS NOT NULL
                      AND ROWNUM = 1
                    )
                  ) AS title,-- 业务描述
                  task.task_id,-- 参与的第一个环节 taskid
                  task.ht_name_zh,-- 参与的第一个环节名称
                  task.TOP_FLOW_ID,-- 参与的第一个环节的 TOP_FLOW_ID
                  task.template_Name as process,-- 流程英文名称
                  task.TEMPLATE_NAME_ZH as processName,-- 流程中文名
                  task.STATUS,-- 参与的第一个环节的状态
                  task.commtask_Code,-- 单据编号
                  task.ht_name, -- 参与的第一个环节英文名
                  task.XMMC, -- 参与的第一个环节项目名称
                  b.name  as FQBM,-- 发起部门名称
                  c.REALNAME as fqr ,--发起人名称
                  task.flow_id,
                  TO_CHAR (
                    task.creater_time,
                    'yyyy-mm-dd hh24:mi:ss'
                  ) AS create_time  -- 参与的第一个环节创建时间
                FROM
                  (
                    SELECT
                      MIN (ID) AS ID,--参与的第一个环节,把min 去掉就可以查到某用户参与的所有环节了
                      PIID,    -- 流程 piid
                      DATE_CREATED,--流程创建时间
                      endTime,--流程结束时间
                      USERNAME AS cyusername--参与人username,

                    FROM
                      (
                        SELECT
                          TASK. ID,
                          task.PIID,
                          TO_CHAR (
                            AHP.START_TIME_,
                            'yyyy-mm-dd hh24:mi:ss'
                          ) DATE_CREATED,
                          TO_CHAR (
                            AHP.END_TIME_,
                            'yyyy-mm-dd hh24:mi:ss'
                          ) AS endTime,
                          EE.USERNAME
                        FROM
                          COMMON_TASK task,
                          ACT_HI_PROCINST ahp,
                          STAFF_MEMBER  ee
                        WHERE
                          task.piid = AHP.PROC_INST_ID_
                        AND EE. ID = TASK.OPERATOR_ID
                      )
                    GROUP BY
                      PIID,
                      DATE_CREATED,
                      endTime,
                      USERNAME
                    ORDER BY
                      date_created DESC
                  ) cyview
                LEFT OUTER JOIN  COMMON_TASK task ON task.id = cyview.id
                LEFT OUTER JOIN department  b ON task.CREATER_DPT_ID = b. ID
                LEFT OUTER JOIN STAFF_MEMBER  c ON task.CREATER_ID = c. ID

                LEFT OUTER JOIN WINDOW_CONFIG D ON TASK.HT_NAME = D .STEP_NAME
                WHERE
                  D .IS_START_TASK = 1

    """
        list << str1
*/
        return list


    }

}

